#!/usr/bin/env python
# -*- coding:utf-8 -*-
# @Time    : 2024/3/26 17:47
# @Author  : jiangwei
# @Site    : 
# @File    : parse_zip.py
# @Software: PyCharm
import sys
import os

BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
sys.path.append(BASE_DIR)

import xlrd2
import hashlib
import zipfile
from datetime import datetime
from pipeline.spider_sql import ShopeeMySqlDB
from utils.send_qiwei import send_user


def map_col(title_excel):
    """
    映射表头信息
    图片	供方货号	供方货号备注	末级分类	供应状态	SKC	商品层次	商品标签	商城上架状态	SKU	属性集	今日销量	SKU近七天销量	SKU近七天中东销量	SKC近七天销量	本月销量	上月销量	已下单未发货数	在途数量	库存数量	可售天数	是否低于预警天数	预测日销	商家实际库存	商家可用库存	商家可售库存	可售库存来源	是否分SKU定价	建议备货数量	价格	上架日期	下架日期	60天客退量	质量等级	是否存在复色SKC	SPU	SHEIN仓备货建议下单数	最小商家仓现货建议数	最大商家仓现货建议数	侵权层级	VMI货期	VMI备货天数	待入库总数	退货待入库数	生产待入库数	其他待入库数	备货标准	下一期备货标准值
    :param title_excel:
    :return:
    """
    key_dict = {
        "图片": "picture",
        "供方货号": "supplier_code",
        "供方货号备注": "supplier_code_remark",
        "末级分类": "last_tag",
        "供应状态": "supplier_status",
        "SKC": "skc",
        "商品层次": "product_level",
        "商品标签": "product_tag",
        "商城上架状态": "shelf_status",
        "SKU": "sku",
        "属性集": "attribute",
        "今日销量": "sale_today",
        "SKU近七天销量": "sku_sale_7days",
        "SKU近七天中东销量": "sku_sale_7days_zhongdong",
        "SKC近七天销量": "skc_sale_7days",
        "本月销量": "sale_month",
        "上月销量": "sale_last_month",
        "已下单未发货数": "order_not_delivered",
        "在途数量": "quantity_in_transit",
        "库存数量": "quantity_in_stock",
        "可售天数": "saleable_days",
        "是否低于预警天数": "is_lower_days_warning",
        "预测日销": "expectation_sale_daily",
        "商家实际库存": "merchant_real_inventory",
        "商家可用库存": "merchant_usable_inventory",
        "商家可售库存": "merchant_available_inventory",
        "可售库存来源": "available_inventory_source",
        "是否分SKU定价": "has_sku_price",
        "建议备货数量": "recommended_quantity_stock",
        "价格": "price",
        "上架日期": "shelf_create_time",
        "下架日期": "shelf_down_time",
        "60天客退量": "refund_60days",
        "质量等级": "quality_level",
        "是否存在复色SKC": "multi_color_SKC",
        "SPU": "spu",
        "SHEIN仓备货建议下单数": "shein_warehouse_stock_recommend",
        "最小商家仓现货建议数": "min_seller_warehouse_stock_suggestion",
        "最大商家仓现货建议数": "max_seller_warehouse_stock_suggestion",
        "侵权层级": "infringement_level",
        "VMI货期": "VMI_delivery_date",
        "VMI备货天数": "VMI_inventory_days",
        "待入库总数": "wait_stored_num",
        "退货待入库数": "returned_wait_stored_num",
        "生产待入库数": "production_wait_stored_num",
        "其他待入库数": "other_wait_stored_num",
        "备货标准": "stock_standard",
        "下一期备货标准值": "next_phase_stock_standard",
    }
    key_list = []
    for i in range(len(title_excel)):
        value = title_excel[i]
        if value and key_dict.get(value):
            key_list.append(key_dict[value])
    return key_list


def encode_key(value_dict):
    """
    将单条数据按md5加密，当作sql的primary-key
    :param key_list:
    :return:
    """
    value_str = ""
    for k, v in value_dict.items():
        if v:
            value_str += str(v)

    new_md5 = hashlib.md5()
    new_md5.update(value_str.encode(encoding='utf-8'))

    return new_md5.hexdigest()


def map_suborder_new_col(title_excel):
    """
    20240808表更改
       图片链接	供货方号	SKC	SPU	商品分类	上架天数	上架日期	供应状态	上架状态	销售模式	质量等级	商品层次	备货标准	商品标签	备注	SKU	属性集	商家SKU	价格	预测日销	当天销量	当天购买单数	近7天销量	近7天中东销量	SKC近7天销量	近30天销量	待发货	         待上架	在途	 SHEIN仓库存	海外仓在库数量	预占数	待入库	商家仓可用库存	商家仓可售库存	最小现货建议数	最大现货建议数	预计急采数	库存可售天数	可售天数	货期	备货天数	建议下单数	已下单数	拟下单数	自动下单(空、否都不自动下单)
    20240827表头
      图片链接	货号	   SKC	SPU	商品分类	上架天数	上架日期	供应状态	上架状态	销售模式	质量等级	商品层次	备货标准	商品标签	备注	SKU	属性集	商家SKU	价格	预测日销	当天销量	当天购买单数	近7天销量	近7天中东销量	SKC近7天销量	近30天销量	待发货	在途库存	待上架	    SHEIN仓库存	海外仓在库数量	预占数	待入库	商家仓可用库存	商家仓可售库存	最小现货建议数	最大现货建议数	预计急采数	库存可售天数	可售天数	货期	备货天数	建议下单数	已下单数	                拟下单数	自动下单(空、否都不自动下单)
     图片链接	货号 	SKC	SPU	商品分类	上架天数	上架日期	供应状态	上架状态	销售模式	质量等级	商品层次	备货标准	商品标签	备注	SKU	属性集	商家SKU	价格	预测日销	当天销量	当天购买单数	近7天销量	近7天中东销量	SKC近7天销量	近30天销量	待发货	在途库存	待上架	  SHEIN仓库存	海外仓在库数量	预占数	待入库	商家仓可用库存	商家仓可售库存	最小现货建议数	最大现货建议数	预计急采数	库存可售天数	可售天数	货期	备货天数	建议下单数	已下单数	低销尾品不允许下单	拟下单数	自动下单(空、否都不自动下单)
    20241213表头
     图片链接	供货方号	SKC	SPU	商品分类	上架天数	上架日期	     供应状态	上架状态	销售模式	质量等级	商品层次	备货标准	商品标签	  备注	SKU	属性集	商家SKU	价格	预测日销	当天销量	当天购买单数	近7天销量	近7天中东销量	SKC近7天销量	近30天销量	待发货	在途	    待上架	  SHEIN仓库存	海外仓在库数量	预占数	待入库	商家仓可用库存	 商家仓可售库存	最小现货建议数	  最大现货建议数	预计急采数	库存可售天数	可售天数	货期	备货天数	建议下单数	已下单数	低销尾品不允许下单	拟下单数	自动下单(空、否都不自动下单)
    20250331表头
     图片链接     供货方号  SKC  SPU  商品分类  上架天数  上架日期  供应状态  上架状态    销售模式  质量等级  商品层次  备货标准  商品标签  备注  SKU  属性集  商家SKU  价格  预测日销  当天销量  当天购买单数  近7天销量  近7天中东销量  SKC近7天销量  近30天销量  待发货     在途  待上架       SHEIN仓库存     海外仓在库数量  预占数  待入库  商家仓总库存   虚拟销售库存      最小现货建议数  最大现货建议数   预计急采数  库存可售天数  可售天数  货期  备货天数  建议下单数  已下单数  低销尾品不允许下单  拟下单数  自动下单(空、否都不自动下单)

    20250725表头
    图片链接	    供货方号	SKC	 SPU   商品分类	上架天数	上架日期	供应状态	上架状态	   销售模式	质量等级	商品层次	备货标准	商品标签	备注	  SKU	属性集	商家SKU	价格	  预测日销	当天销量	当天购买单数	近7天销量	近7天中东销量	SKC近7天销量	近30天销量	待发货	在途	待上架	SHEIN仓库存	         海外仓在库数量 预占数	待入库	商家仓总库存	虚拟销售库存	最小现货建议数	最大现货建议数	    预计急采数	库存可售天数	可售天数	货期	备货天数	建议下单数  系统下单数	商家下单数	低销尾品不允许下单	拟下单数	自动下单(空、否都不自动下单)

    2025.8.25表头
    图片链接	    供货方号	SKC	SPU	   商品分类	上架天数	上架日期	供应状态	上架状态	   销售模式	质量等级	商品层次	备货标准	商品标签	备注	  SKU	属性集	商家SKU	供货价 预测日销	当天销量	当天购买单数	近7天销量	近7天中东销量	SKC近7天销量	近30天销量	待发货	在途	待上架	SHEIN仓库存	         海外仓在库数量 预占数	待入库	商家仓总库存	虚拟销售库存	最小现货建议数	最大现货建议数	    预计急采数	库存可售天数	可售天数	货期	备货天数	建议下单数	系统下单数	商家下单数	低销尾品不允许下单	拟下单数	自动下单(空、否都不自动下单)

    2025.9.25表头
    图片链接	    供货方号	SKC	SPU	   商品分类	上架天数	上架日期	供应状态	上架状态	    销售模式	质量等级	商品层次	备货标准	商品标签	备注	SKU	    属性集	商家SKU	供货价 币种	预测日销	当天销量	当天购买单数	近7天销量 近7天中东销量	SKC近7天销量	近30天销量	待发货	在途	待上架	SHEIN仓库存	         海外仓在库数量	预占数	待入库	商家仓总库存	虚拟销售库存	最小现货建议数	最大现货建议数	预计急采数	库存可售天数	可售天数	货期	备货天数	建议下单数	系统下单数	商家下单数	低销尾品不允许下单	拟下单数	自动下单(空、否都不自动下单)

    :return:
    """
    key_dict = {
        "图片链接": "picture",
        "供货方号": "supplier_code",
        # "货号": "supplier_code",
        "SKC": "skc",
        "SPU": "spu",
        "商品分类": "goods_category",
        "上架天数": "shelf_days",
        "上架日期": "shelf_create_time",
        "供应状态": "supplier_status",
        "上架状态": "shelf_status",
        "销售模式": "sale_mode",
        "质量等级": "quality_level",
        "商品层次": "product_level",
        "备货标准": "stock_standard",
        "商品标签": "product_tag",
        "备注": "remark",
        "SKU": "sku",
        "属性集": "attribute",
        "商家SKU": "mall_sku",
        "供货价": "price",
        "预测日销": "expectation_sale_daily",
        "当天销量": "sale_today",
        "当天购买单数": "purchase_num_today",
        "近7天销量": "sku_sale_7days",
        "近7天中东销量": "sku_sale_7days_zhongdong",
        "SKC近7天销量": "skc_sale_7days",
        "近30天销量": "sale_30days",
        "待发货": "waiting_sent",
        "在途": "in_transit",
        # "在途库存": "in_transit",
        "待上架": "waiting_shelf",
        "SHEIN仓库存": "shein_warehouse_storage",
        "海外仓在库数量": "overseas_warehouses_in_storage_num",
        "预占数": "preemption_num",
        "待入库": "wait_stored",
        "商家仓可用库存": "merchant_warehouse_available_inventory",
        "商家仓可售库存": "merchant_warehouses_sale_available_inventory",
        "最小现货建议数": "min_seller_warehouse_stock_suggestion",
        "最大现货建议数": "max_seller_warehouse_stock_suggestion",
        "预计急采数": "expected_rush_production",
        "库存可售天数": "inventory_available_saleable_days",
        "可售天数": "saleable_days",
        "货期": "delivery_date",
        "备货天数": "inventory_days",
        "建议下单数": "suggested_order_num",

        # 2025.7.25已取消【已下单数】字段，新增【系统下单数，商家下单数】字段但不使用
        # "已下单数": "has_order_num",
        "系统下单数": "system_order_num",
        "商家下单数": "merchant_order_num",

        "低销尾品不允许下单": "sale_lower_no_order",
        "拟下单数": "ready_order_num",
        "自动下单(空、否都不自动下单)": "auto_order",
        "商家仓总库存": "merchant_warehouse_available_inventory",  # 20250331新增字段
        "虚拟销售库存": "merchant_warehouses_sale_available_inventory",  # 20250331新增字段

    }
    # 处理排除字段
    exclude_fields = ['币种']

    if exclude_fields is None:
        exclude_fields = []

    # 创建过滤后的表头列表（排除指定字段）
    filtered_title = [col for col in title_excel if col not in exclude_fields]

    key_list = []
    for value in filtered_title:
        if value and key_dict.get(value):
            key_list.append(key_dict[value])
        else:
            # 对于不在映射字典中的字段，保留原值（可选处理）
            # key_list.append(value)  # 如果希望保留未映射字段
            pass  # 或者直接跳过

    # 检查映射完整性（基于过滤后的表头）
    if len(key_list) != len(filtered_title):
        send_user(content="shein_seller_sub_order_new报表表头字段更改导致存储异常，请开发跟业务确认字段。")
        print("----**  ATTENTION ** ---- title -to- sql not match")
        return []
    return key_list


def map_inventory_col(title_excel):
    """
    变动类型	结算类型	供方货号	SKC	平台SKU	商家SKU	属性集	数量	   单价	金额	币种	业务单号	单据号	添加时间	供应商ID	供应商名称	                          备注	活动信息

    2025年6月27日表头更改
    变动类型 结算类型  供方货号  SKC  平台SKU  商家SKU  属性集  数量  单价  金额  币种  业务单号  单据号  添加时间  供应商ID  供应商名称  销售商家ID  销售商家名称  备注  活动信息

    :param title_excel:
    :return:
    """
    key_dict = {
        "变动类型": "change_type",
        "结算类型": "settlement_type",
        "供方货号": "supplier_code",
        "SKC": "skc",
        "平台SKU": "platform_sku",
        "商家SKU": "mall_sku",
        "属性集": "attribute",
        "数量": "count",
        "单价": "price",
        "金额": "amount",
        "币种": "currency",
        "业务单号": "order_number",
        "单据号": "business_number",
        "添加时间": "add_date",
        "供应商ID": "supplier_id",
        "供应商名称": "supplier_name",
        "备注": "remark",
        "活动信息": "message",

        # 2025年6月27日新增字段
        "销售商家ID": "selling_merchant_id",
        "销售商家名称": "selling_merchant_name",

    }
    key_list = []
    for i in range(len(title_excel)):
        value = title_excel[i]
        if value and key_dict.get(value):
            key_list.append(key_dict[value])

    if len(key_list) != len(title_excel):
        send_user(content="shein_seller_inventory报表表头字段更改导致存储异常，请开发跟业务确认字段。")
        print("----**  ATTENTION ** ---- title -to- sql not match")
        return []
    return key_list


def map_day_detail_col(title_excel):
    """
    供应商名称	日期	SKC	供方货号	平台SKU	商家SKU	属性集	期初库存数	期初库存金额	入库数	入库金额	出库数	出库金额	期末库存数	期末库存金额

    :return:
    """
    key_dict = {
        "供应商名称": "supplier_name",
        "日期": "date",
        "SKC": "skc",
        "供方货号": "supplier_code",
        "平台SKU": "platform_sku",
        "商家SKU": "mall_sku",
        "属性集": "attribute",
        "期初库存数": "opening_inventory",
        "期初库存金额": "opening_inventory_amount",
        "入库数": "in_count",
        "入库金额": "in_amount",
        "出库数": "out_count",
        "出库金额": "out_amount",
        "期末库存数": "ending_inventory",
        "期末库存金额": "ending_inventory_amount",

    }
    key_list = []
    for i in range(len(title_excel)):
        value = title_excel[i]
        if value and key_dict.get(value):
            key_list.append(key_dict[value])

    if len(key_list) != len(title_excel):
        send_user(content="shein_seller_day_detail报表表头字段更改导致存储异常，请开发跟业务确认字段。")
        print("----**  ATTENTION ** ---- title -to- sql not match")
        return []
    return key_list


def check_zip_or_csv(target_excel, download_dir=BASE_DIR, file_list=[]):
    """
    先判断是否为zip文件，不是的话直接解析数据
    :return:
    """
    if ".zip" in target_excel:
        try:
            with zipfile.ZipFile(target_excel) as zip_file:
                for name in zip_file.namelist():  # 解压文件
                    zip_file.extract(name, download_dir)
                    file = os.path.join(download_dir + "/" + name)
                    if ".xlsx" in file and file not in file_list:
                        file_list.append(file)

            return check_zip_or_csv(target_excel=file, download_dir=download_dir, file_list=file_list)

        except Exception as e:
            print("--{} : check_zip_or_csv ERROR: {}".format(target_excel, str(e)))
            os.remove(target_excel)
            return False

    else:
        file_list.append(target_excel)

    return list(set(file_list))


def parse_shein_suborder_file(shop, target_zip):
    """
    解析shein_suborder文件
    :param file_name:
    :return:
    """
    file_name = check_zip_or_csv(target_zip, download_dir=BASE_DIR, file_list=[])[0]
    if isinstance(file_name, bool):
        return

    result_dct_ls = list()
    workbook = xlrd2.open_workbook(file_name)  # 打开工作簿
    sheets = workbook.sheet_names()  # 获取工作簿中的所有表格
    worksheet = workbook.sheet_by_name(sheets[0])  # 获取工作簿中所有表格中的的第一个表格
    date = datetime.now().strftime('%Y-%m-%d %H:%M:%S').split(" ")[0]
    excel_title = [i.value for i in worksheet.row(0)]
    print(excel_title)
    # 获取字段映射
    key_list = map_suborder_new_col(excel_title)
    print(key_list)
    if key_list:
        for i in range(1, worksheet.nrows):
            v = worksheet.row(i)
            result_dct = {}
            for j in range(len(key_list)):
                key = key_list[j]
                # 要剔除的字段
                if key not in ["sale_lower_no_order","system_order_num","merchant_order_num"]:
                    result_dct.update({
                        key: v[j].value
                    })
            result_dct.update({
                "date": date,
                "shop": shop
            })
            result_dct.update({
                "primary_key": encode_key(result_dct),  # 主键
            })
            result_dct_ls.append(result_dct)

    print("总数据量:{}".format(len(result_dct_ls)))
    return result_dct_ls


def parse_shein_inventory_file(shop, target_zip):
    """
    解析  台账变动明细
    :param shop:
    :param target_zip:
    :return:
    """
    file_name = check_zip_or_csv(target_zip, download_dir=BASE_DIR, file_list=[])[0]
    if isinstance(file_name, bool):
        return

    result_dct_ls = list()
    workbook = xlrd2.open_workbook(file_name)  # 打开工作簿
    sheets = workbook.sheet_names()  # 获取工作簿中的所有表格
    worksheet = workbook.sheet_by_name(sheets[0])  # 获取工作簿中所有表格中的的第一个表格
    excel_title = [i.value for i in worksheet.row(0)]
    print(excel_title)
    key_list = map_inventory_col(excel_title)
    print(key_list)
    if key_list:
        for i in range(1, worksheet.nrows):
            v = worksheet.row(i)
            result_dct = {}
            for j in range(len(key_list)):
                key = key_list[j]
                if key not in ['selling_merchant_id', 'selling_merchant_name']:
                    result_dct.update({
                        key: v[j].value
                    })
            result_dct.update({
                "date": result_dct.get("add_date").split(" ")[0],
                "shop": shop
            })
            result_dct.update({
                "primary_key": encode_key(result_dct),  # 主键
            })
            print(i, "----", result_dct)
            result_dct_ls.append(result_dct)

    print("总数据量:{}".format(len(result_dct_ls)))
    return result_dct_ls


def parse_shein_day_detail_file(shop, target_zip):
    """
    解析日报明细
    :param shop:
    :param target_zip:
    :return:
    """
    file_name = check_zip_or_csv(target_zip, download_dir=BASE_DIR, file_list=[])[0]
    if isinstance(file_name, bool):
        return

    result_dct_ls = list()
    workbook = xlrd2.open_workbook(file_name)  # 打开工作簿
    sheets = workbook.sheet_names()  # 获取工作簿中的所有表格
    worksheet = workbook.sheet_by_name(sheets[0])  # 获取工作簿中所有表格中的的第一个表格
    excel_title = [i.value for i in worksheet.row(0)]
    print(excel_title)
    key_list = map_day_detail_col(excel_title)
    print(key_list)
    if key_list:
        for i in range(1, worksheet.nrows):
            v = worksheet.row(i)
            result_dct = {}
            for j in range(len(key_list)):
                key = key_list[j]
                result_dct.update({
                    key: v[j].value
                })
            result_dct.update({
                "shop": shop
            })
            result_dct.update({
                "primary_key": encode_key(result_dct),  # 主键
            })
            print(i, "-----", result_dct)
            result_dct_ls.append(result_dct)

    print("总数据量:{}".format(len(result_dct_ls)))
    return result_dct_ls


def parse_file(shop, file_path):
    """
    解析文件
    :param shop:
    :param file_path:
    :return:
    """
    if "suborder" in file_path:
        data_ls = parse_shein_suborder_file(shop, file_path)
        return data_ls

    elif "inventory" in file_path:
        data_ls = parse_shein_inventory_file(shop, file_path)
        return data_ls

    elif "daydetail" in file_path:
        data_ls = parse_shein_day_detail_file(shop, file_path)
        return data_ls

    else:
        return []


def map_table_name(file_path):
    """
    映射表名
    :param file_path:
    :return:
    """
    if "suborder" in file_path:
        return "shein_seller_sub_order_new"

    elif "inventory" in file_path:
        return "shein_seller_inventory"

    elif "daydetail" in file_path:
        return "shein_seller_day_detail"

    else:
        return ""


def excel_to_sql(shop, file_path, sql_obj=None):
    """
    执行文件解析入库
    :param file_path:
    :return:
    """
    if not sql_obj:
        sql_obj = ShopeeMySqlDB()

    data_ls = parse_file(shop, file_path)
    print(f"数据：————————{data_ls}")
    if data_ls:
        i = 0
        result_dct_ls = []
        for data in data_ls:
            msg = []
            key_list = []
            i += 1
            for k, v in data.items():
                # print(k, "---", type(v))
                msg.append(v)
                key_list.append(k)
            value = tuple(msg)
            result_dct_ls.append(value)

        table = map_table_name(file_path)
        if not table:
            return False
        # 入库
        save_end = sql_obj.execute_insert_many(table=table, key_list=key_list, result_dct_ls=result_dct_ls,
                                               replace_mode=False)
        print("target_excel:{} ,--save end, {}".format(file_path, len(result_dct_ls)))
        return save_end
    return False


if __name__ == '__main__':
    shop_list = ["US_SN1_CB", "US_SN2_CB"]
    # 记得改账号！！！
    excel_to_sql(shop="US_SN2_CB", file_path="../US_SN2_CB_2025-07-25_shein_suborder.zip")
